﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace iHub
{
    class EmployeeManager : dbConnector
    {
        private SqlDataReader read;
        private SqlCommand command;
        private SqlParameter param;
        private AssignmentManager assm = new AssignmentManager();

        public void InsertEmployee(Employee newEmployee)
        {
            try
            {
                connect();

                command = new SqlCommand("dbo.AddEmployee", getCon());
                command.CommandType = CommandType.StoredProcedure;

                //insert paramaters / values

                param = new SqlParameter("@tin", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.TIN;
                command.Parameters.Add(param);
                param = new SqlParameter("@name", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.Name;
                command.Parameters.Add(param);
                param = new SqlParameter("@address", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.Address;
                command.Parameters.Add(param);
                param = new SqlParameter("@city", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.City;
                command.Parameters.Add(param);
                param = new SqlParameter("@contact", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.ContactNo;
                command.Parameters.Add(param);
                param = new SqlParameter("@status", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.Status;
                command.Parameters.Add(param);

                command.ExecuteNonQuery();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public void EditEmployee(Employee newEmployee)
        {
            Assignment newAssignment = new Assignment();
            try
            {
                connect();

                command = new SqlCommand("dbo.EditEmployee", getCon());
                command.CommandType = CommandType.StoredProcedure;

                //insert paramaters / values
                param = new SqlParameter("@idno", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.IdNo;
                command.Parameters.Add(param);
                param = new SqlParameter("@tin", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.TIN;
                command.Parameters.Add(param);
                param = new SqlParameter("@name", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.Name;
                command.Parameters.Add(param);
                param = new SqlParameter("@address", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.Address;
                command.Parameters.Add(param);
                param = new SqlParameter("@city", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.City;
                command.Parameters.Add(param);
                param = new SqlParameter("@contact", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.ContactNo;
                command.Parameters.Add(param);

                command.ExecuteNonQuery();

                disconnect();

                newAssignment.Eid = newEmployee.IdNo;
                newAssignment.Ename = newEmployee.Name;
                assm.UpdateAssign(newAssignment);
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public void TerminateEmployee(Employee newEmployee)
        {
            try
            {
                connect();

                command = new SqlCommand("dbo.TerminateEmployee", getCon());
                command.CommandType = CommandType.StoredProcedure;

                //insert paramaters / values
                param = new SqlParameter("@idno", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.IdNo;
                command.Parameters.Add(param);
                param = new SqlParameter("@name", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.Name;
                command.Parameters.Add(param);
                param = new SqlParameter("@status", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.Status;
                command.Parameters.Add(param);

                command.ExecuteNonQuery();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public void HireEmployee(Employee newEmployee)
        {
            try
            {
                connect();

                command = new SqlCommand("dbo.HireEmployee", getCon());
                command.CommandType = CommandType.StoredProcedure;

                //insert paramaters / values
                param = new SqlParameter("@idno", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.IdNo;
                command.Parameters.Add(param);
                param = new SqlParameter("@name", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.Name;
                command.Parameters.Add(param);
                param = new SqlParameter("@status", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newEmployee.Status;
                command.Parameters.Add(param);

                command.ExecuteNonQuery();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public List<string> ViewEmployee()
        {
            List<string> _items = new List<string>();
            try
            {
                connect();

                command = new SqlCommand("select * from Employee ORDER by e_name ASC", getCon());

                read = command.ExecuteReader();
                String name1, dash = "-";
                String item;
                int id1;

                while (read.Read())
                {
                    name1 = (string)read.GetValue(2);
                    name1 = name1 += dash;
                    id1 = (int)read.GetValue(0);
                    item = name1 += id1;

                    //Convert.ToString(id1);

                    _items.Add(item);
                }

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return _items;
        }

        public String EmployeeTIN(String i)
        {
            String id2 = i, value="";

            try
            {
                connect();
                command = new SqlCommand("select * from Employee where e_id = @id2", getCon());
                param = new SqlParameter("@id2", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = id2;
                command.Parameters.Add(param);
                // string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";

                read = command.ExecuteReader();
                read.Read();

                value = (string)read.GetValue(1);
                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return value;
        }

        public String EmployeeAddress(String i)
        {
            String id2 = i, value="";
            //string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";

            try
            {
                connect();

                command = new SqlCommand("select * from Employee where e_id = @id2", getCon());
                param = new SqlParameter("@id2", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = id2;
                command.Parameters.Add(param);
                read = command.ExecuteReader();
                read.Read();


                value = (string)read.GetValue(3);

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return value;
        }

        public String EmployeeCity(String i)
        {
            String id2 = i, value="";
            // string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";

            try
            {
                connect();

                command = new SqlCommand("select * from Employee where e_id = @id2", getCon());
                param = new SqlParameter("@id2", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = id2;
                command.Parameters.Add(param);
                read = command.ExecuteReader();
                read.Read();

                value = (string)read.GetValue(4);
                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
            return value;
        }

        public string EmployeeContact(String i)
        {
            String id2 = i;
            string value="";

            try
            {
                connect();
                //    string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";

                command = new SqlCommand("select * from Employee where e_id = @id2", getCon());
                param = new SqlParameter("@id2", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = id2;
                command.Parameters.Add(param);
                read = command.ExecuteReader();
                read.Read();

                value = Convert.ToString(read.GetValue(5));

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return value;
        }

        public Boolean EmployeeBoolean(String i)
        {
            String id2 = i;
            Boolean value=false;

            try
            {
                connect();
                //string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";

                command = new SqlCommand("select * from Employee where e_id = @id2", getCon());
                param = new SqlParameter("@id2", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = id2;
                command.Parameters.Add(param);
                read = command.ExecuteReader();
                read.Read();

                value = (Boolean)read.GetValue(6);

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return value;
        }

        public List<string> ViewAssignments(string i)
        {
            String id2 = i;
            String request;
            String assign;
            String status;
            String dash;
            String item1;
            List<string> _items = new List<string>();

            try
            {
                connect();
                // string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";

                command = new SqlCommand("select * from Assignment where e_id = @id2", getCon());
                param = new SqlParameter("@id2", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = id2;
                command.Parameters.Add(param);
                read = command.ExecuteReader();
                while (read.Read())
                {
                    request = (string)read.GetValue(3);
                    assign = (string)read.GetValue(4);
                    status = (string)read.GetValue(6);
                    dash = "-";
                    item1 = request += dash += assign += dash += status;
                    _items.Add(item1);
                }

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return _items;
        }

        public List<string> ViewAccAssignments(string i)
        {
            String id2 = i;
            List<string> _items = new List<string>();
            String request;
            String assign;
            String status;
            String dash;
            String item1;
            int id;
            String id3;
            DataTable accassigns = new DataTable();
            DataRow row;
            int no;
            int x;
            SqlDataReader r2;
            SqlCommand com2;

            x = 0;
            try
            {
                connect();
                // string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";
                accassigns.Columns.Add(new DataColumn("AssignNo", typeof(int)));
                accassigns.Columns.Add(new DataColumn("Name", typeof(string)));

                command = new SqlCommand("select * from Assignment where e_id = @id2 and status = 'Complete'", getCon());
                param = new SqlParameter("@id2", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = id2;
                command.Parameters.Add(param);
                read = command.ExecuteReader();

                while (read.Read())
                {
                    id = (int)read.GetValue(0);
                    row = accassigns.NewRow();
                    id3 = id.ToString();
                    request = (string)read.GetValue(7);
                    assign = (string)read.GetValue(8);
                    status = (string)read.GetValue(10);
                    dash = "-";
                    item1 = id3 += dash += request += dash += assign += dash += status;
                    row["AssignNo"] = id;
                    row["Name"] = item1;
                    accassigns.Rows.Add(row);
                }

                read.Close();

                com2 = new SqlCommand("select * from Payroll_Assignment", getCon());
                r2 = com2.ExecuteReader();

                while (r2.Read())
                {
                    x = 0;
                    no = Convert.ToInt32(r2["assign_id"]);

                    while (x < accassigns.Rows.Count)
                    {
                        if (no == (int)accassigns.Rows[x]["AssignNo"])
                        {
                            accassigns.Rows[x].Delete();
                        }
                        x++;
                    }
                }

                x = 0;
                foreach (DataRow drow in accassigns.Rows)
                {
                    _items.Add((string)accassigns.Rows[x]["Name"]);
                    x++;
                }

                r2.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return _items;
        }

        public Boolean verifyEmployee(int id)
        {
            Boolean check = false;

            try
            {
                connect();
                command = new SqlCommand("select * from Employee where e_id = @id", getCon());
                param = new SqlParameter("@id", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = id;
                command.Parameters.Add(param);
                read = command.ExecuteReader();

                if (read.HasRows)
                {
                    check = true;
                }
                else
                {
                    check = false;
                }

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
            return check;
        }

        public string getEmployeeName(int id)
        {
            string ename="";

            try
            {
                connect();
                command = new SqlCommand("select e_name from Employee where e_id = @id", getCon());
                param = new SqlParameter("@id", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = id;
                command.Parameters.Add(param);
                read = command.ExecuteReader();

                read.Read();
                ename = Convert.ToString(read.GetValue(0));
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return ename;
        }
    }
}
